﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Remoting.Contexts;
using System.Web;
using ORM.Business.Feedbacks;
using ORM.Business.Interfaces;
using ORM.Business.Models;
using ORM.Business.Models.Entities;
using ORM.Common.Logging;
using ORM.Resources;
using System.Data.Entity;


namespace ORM.Business.Repositories
{
    public class QuestionRepository : IQuestionRepository
    {
        #region Q&A
        public QuestionListFeedback GetAllQuestions()
        {

            try
            {
               
                using (var ORMDbContext = new OrmEntities())
                {
                    var query = from q in  ORMDbContext.Questions
                                join u in ORMDbContext.Users on q.PostedById equals u.Id
                                join m in ORMDbContext.Models on q.ModelId equals m.Id
                                join d in ORMDbContext.Devices on m.DeviceId equals d.Id
                                join mf in ORMDbContext.Manufacturers on m.ManufacturerId equals mf.Id
                                where !q.IsDeleted 
                                orderby q.PostedDate descending
                                select new
                                {
                                    q.Id,
                                    q.Title,
                                    q.TitleUrl,
                                    q.PostedDate,
                                    q.Views,
                                    q.Point,
                                    q.UpVotes,
                                    q.DownVotes,
                                    q.ContentHtml,
                                    q.Answers,
                                    q.PostedById,
                                    u.Username,
                                    q.ModelId,
                                    DeviceId = d.Id,
                                    ManufacturerId = mf.Id,
                                    q.IsResolved,
                                    ModelName = m.Name,
                                    DeviceName = d.Name,
                                    ManufacturerName = mf.Name
                                };

                    var total = query.Count();
                    var questionList = new List<Question>();
                    foreach (var question in query)
                    {
                        questionList.Add(new Question
                        {
                            Id = question.Id,
                            Title = question.Title,
                            TitleUrl = question.TitleUrl,
                            PostedDate = question.PostedDate,
                            Views = question.Views,
                            Point = question.Point,
                            UpVotes = question.UpVotes,
                            DownVotes = question.DownVotes,
                            ContentHtml = question.ContentHtml,
                            Answers = question.Answers,
                            IsResolved = question.IsResolved,
                            PostedBy = new user { Id = question.PostedById, Username = question.Username },
                            //ModelWithAllInfo = new ModelWithAllInformation { Id = question.ModelId, ModelName = question.ModelName, DeviceName = question.DeviceName, ManufactureName = question.ManufacturerName }
                            Model = new Model { Id = question.ModelId, Name = question.ModelName, Manufacturer = new Manufacturer { Name = question.ManufacturerName }, Device = new Device { Name = question.DeviceName } }
                        });
                    }
                    return new QuestionListFeedback(true, null, questionList, total);
                }

            }
            catch (Exception e)
            {
                 Logger.Error(e);
                return new QuestionListFeedback(false, "Cannot get list question");
            }
        }

        public Feedback<IList<Question>> GetTopNewQuestions()
        {

            try
            {

                using (var ORMDbContext = new OrmEntities())
                {
                    var query = from q in ORMDbContext.Questions
                                //join u in ORMDbContext.Users on q.PostedById equals u.Id
                                //join m in ORMDbContext.Models on q.ModelId equals m.Id
                                //join d in ORMDbContext.Devices on m.DeviceId equals d.Id
                                //join mf in ORMDbContext.Manufacturers on m.ManufacturerId equals mf.Id
                                where !q.IsDeleted
                                orderby q.PostedDate descending
                                select new
                                {
                                    q.Id,
                                    q.Title,
                                    //q.TitleUrl,
                                    //q.PostedDate,
                                    //q.Views,
                                    //q.Point,
                                    //q.UpVotes,
                                    //q.DownVotes,
                                    //q.ContentHtml,
                                    //q.Answers,
                                    //q.PostedById,
                                    //u.Username,
                                    //q.ModelId,
                                    //DeviceId = d.Id,
                                    //ManufacturerId = mf.Id,
                                    //q.IsResolved,
                                    //ModelName = m.Name,
                                    //DeviceName = d.Name,
                                    //ManufacturerName = mf.Name
                                };

                    var questionList = new List<Question>();
                    query = query.Take(KeyObject.TopItemNumber.QuestionNumber);
                    foreach (var question in query)
                    {
                        questionList.Add(new Question
                        {
                            Id = question.Id,
                            Title = question.Title,
                            //TitleUrl = question.TitleUrl,
                            //PostedDate = question.PostedDate,
                            //Views = question.Views,
                            //Point = question.Point,
                            //UpVotes = question.UpVotes,
                            //DownVotes = question.DownVotes,
                            //ContentHtml = question.ContentHtml,
                            //Answers = question.Answers,
                            //IsResolved = question.IsResolved,
                            //PostedBy = new user { Id = question.PostedById, Username = question.Username },
                            ////ModelWithAllInfo = new ModelWithAllInformation { Id = question.ModelId, ModelName = question.ModelName, DeviceName = question.DeviceName, ManufactureName = question.ManufacturerName }
                            //Model = new Model { Id = question.ModelId, Name = question.ModelName, Manufacturer = new Manufacturer { Name = question.ManufacturerName }, Device = new Device { Name = question.DeviceName } }
                        });
                    }
                    return new Feedback<IList<Question>>(true, null, questionList);
                }

            }
            catch (Exception e)
            {
                return new Feedback<IList<Question>>(false, Messages.GeneralError);
            }
        }       
        public QuestionListFeedback GetAllQuestion(PageInfo pageInfo, int deviceId, int manufacturerId, int modelId, string stringToSearch)
        {

            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    if (stringToSearch != null && stringToSearch != "")
                    {
                        //var queryString = "SELECT * FROM Questions WHERE isDeleted='false' and dbo.Levenstein({0}, Title) > 0 order by dbo.Levenstein({0}, Title+convert(nvarchar,ContentHtml )) desc, PostedDate";
                        var queryString =
                            "SELECT p.* FROM dbo.questions AS p INNER JOIN FREETEXTTABLE(questions, Title, {0}, LANGUAGE 'Vietnamese') AS ft ON ft.[KEY] = p.Id and isDeleted = 'false' ORDER BY ft.[Rank] DESC, PostedDate";
                        var query = (ORMDbContext as System.Data.Entity.Infrastructure.IObjectContextAdapter)
                              .ObjectContext.ExecuteStoreQuery<Question>(queryString, stringToSearch).ToList();                           
                            var total = query.Count();
                            var result = query.Skip((pageInfo.PageNo - 1) * pageInfo.PageSize).Take(pageInfo.PageSize);
                            var questionList = new List<Question>();
                            foreach (var question in result)
                            {
                                question.Model = ORMDbContext.Models.SingleOrDefault(m => m.Id == question.ModelId);
                                var device = ORMDbContext.Devices.SingleOrDefault(m => m.Id == question.Model.DeviceId);
                                var manufacturer = ORMDbContext.Manufacturers.SingleOrDefault(m => m.Id == question.Model.ManufacturerId);
                                questionList.Add(new Question
                                {
                                    Id = question.Id,
                                    Title = question.Title,
                                    TitleUrl = question.TitleUrl,
                                    PostedDate = question.PostedDate,
                                    Views = question.Views,
                                    Point = question.Point,
                                    UpVotes = question.UpVotes,
                                    DownVotes = question.DownVotes,
                                    ContentHtml = question.ContentHtml,
                                    Answers = question.Answers,
                                    IsResolved = question.IsResolved,
                                    PostedBy = ORMDbContext.Users.SingleOrDefault(u => u.Id == question.PostedById),
                                    //ModelWithAllInfo = new ModelWithAllInformation { Id = question.ModelId, ModelName = question.ModelName, DeviceName = question.DeviceName, ManufactureName = question.ManufacturerName }   
                                    Model = new Model { Id = question.ModelId, Name = question.Model.Name, Manufacturer = new Manufacturer { Id = manufacturer.Id, Name = manufacturer.Name }, Device = new Device { Id = device.Id, Name = device.Name } },
                                });
                            }
                            return new QuestionListFeedback(true, null, questionList, total);                                                                                           
                    }
                    else
                    {
                       var query = from q in ORMDbContext.Questions
                                    join u in ORMDbContext.Users on q.PostedById equals u.Id
                                    join m in ORMDbContext.Models on q.ModelId equals m.Id
                                    join d in ORMDbContext.Devices on m.DeviceId equals d.Id
                                    join mf in ORMDbContext.Manufacturers on m.ManufacturerId equals mf.Id
                                    where !q.IsDeleted
                                    orderby q.PostedDate descending
                                    select new
                                    {
                                        q.Id,
                                        q.Title,
                                        q.TitleUrl,
                                        q.PostedDate,
                                        q.Views,
                                        q.Point,
                                        q.UpVotes,
                                        q.DownVotes,
                                        q.ContentHtml,
                                        q.Answers,
                                        q.PostedById,
                                        u.Username,
                                        q.ModelId,
                                        DeviceId = d.Id,
                                        ManufacturerId = mf.Id,
                                        q.IsResolved,
                                        ModelName = m.Name,
                                        DeviceName = d.Name,
                                        ManufacturerName = mf.Name
                                    };
                        if (modelId != 0)
                        {
                            query = query.Where(q => q.ModelId == modelId);
                        }
                        else
                        {
                            if (deviceId != 0)
                            {
                                query = query.Where(q => q.DeviceId == deviceId);
                            }
                            if (manufacturerId != 0)
                            {
                                query = query.Where(q => q.ManufacturerId == manufacturerId);
                            }
                        }
                         var total = query.Count();
                    query = query.Skip((pageInfo.PageNo - 1) * pageInfo.PageSize).Take(pageInfo.PageSize);
                    var questionList = new List<Question>();
                    foreach (var question in query)
                    {
                        questionList.Add(new Question
                        {
                            Id = question.Id,
                            Title = question.Title,
                            TitleUrl = question.TitleUrl,
                            PostedDate = question.PostedDate,
                            Views = question.Views,
                            Point = question.Point,
                            UpVotes = question.UpVotes,
                            DownVotes = question.DownVotes,
                            ContentHtml = question.ContentHtml,
                            Answers = question.Answers,
                            IsResolved = question.IsResolved,
                            PostedBy = new user { Id = question.PostedById, Username = question.Username },
                            //ModelWithAllInfo = new ModelWithAllInformation { Id = question.ModelId, ModelName = question.ModelName, DeviceName = question.DeviceName, ManufactureName = question.ManufacturerName }
                            Model = new Model { Id = question.ModelId, Name = question.ModelName, Manufacturer = new Manufacturer { Id = question.ManufacturerId, Name = question.ManufacturerName }, Device = new Device { Id = question.DeviceId, Name = question.DeviceName } },
                        });
                    }
                    return new QuestionListFeedback(true, null, questionList, total);
                        
                    }                   
                   
                }

            }
            catch (Exception e)
            {

                return new QuestionListFeedback(false, "Cannot get list question");
            }
        }
        public QuestionListFeedback GetHelpfulQuestion(PageInfo pageInfo, int deviceId, int manufacturerId, int modelId, string stringToSearch)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    if (stringToSearch != null && stringToSearch != "")
                    {
                        //var queryString = "SELECT * FROM Questions WHERE isDeleted='false' and dbo.Levenstein({0}, Title+convert(nvarchar,ContentHtml )) > 0 order by dbo.Levenstein({0}, Title+convert(nvarchar,ContentHtml )) desc, UpVotes desc";
                        var queryString =
                            "SELECT p.* FROM dbo.questions AS p INNER JOIN FREETEXTTABLE(questions, Title, {0}, LANGUAGE 'Vietnamese') AS ft ON ft.[KEY] = p.Id and isDeleted = 'false' ORDER BY ft.[Rank] DESC, UpVotes desc";
                       
                        var query = (ORMDbContext as System.Data.Entity.Infrastructure.IObjectContextAdapter)
                              .ObjectContext.ExecuteStoreQuery<Question>(queryString, stringToSearch).ToList();
                        var total = query.Count();
                        var result = query.Skip((pageInfo.PageNo - 1) * pageInfo.PageSize).Take(pageInfo.PageSize);
                        var questionList = new List<Question>();
                        foreach (var question in result)
                        {
                            question.Model = ORMDbContext.Models.SingleOrDefault(m => m.Id == question.ModelId);
                            var device = ORMDbContext.Devices.SingleOrDefault(m => m.Id == question.Model.DeviceId);
                            var manufacturer = ORMDbContext.Manufacturers.SingleOrDefault(m => m.Id == question.Model.ManufacturerId);
                            questionList.Add(new Question
                            {
                                Id = question.Id,
                                Title = question.Title,
                                TitleUrl = question.TitleUrl,
                                PostedDate = question.PostedDate,
                                Views = question.Views,
                                Point = question.Point,
                                UpVotes = question.UpVotes,
                                DownVotes = question.DownVotes,
                                ContentHtml = question.ContentHtml,
                                Answers = question.Answers,
                                IsResolved = question.IsResolved,
                                PostedBy = ORMDbContext.Users.SingleOrDefault(u => u.Id == question.PostedById),
                                //ModelWithAllInfo = new ModelWithAllInformation { Id = question.ModelId, ModelName = question.ModelName, DeviceName = question.DeviceName, ManufactureName = question.ManufacturerName }   
                                Model = new Model { Id = question.ModelId, Name = question.Model.Name, Manufacturer = new Manufacturer { Id = manufacturer.Id, Name = manufacturer.Name }, Device = new Device { Id = device.Id, Name = device.Name } },
                            });
                        }
                        return new QuestionListFeedback(true, null, questionList, total);
                    }
                    else
                    {
                        var query = from q in ORMDbContext.Questions
                                    join u in ORMDbContext.Users on q.PostedById equals u.Id
                                    join m in ORMDbContext.Models on q.ModelId equals m.Id
                                    join d in ORMDbContext.Devices on m.DeviceId equals d.Id
                                    join mf in ORMDbContext.Manufacturers on m.ManufacturerId equals mf.Id
                                    where !q.IsDeleted
                                    orderby q.UpVotes descending
                                    select new
                                    {
                                        q.Id,
                                        q.Title,
                                        q.TitleUrl,
                                        q.PostedDate,
                                        q.Views,
                                        q.Point,
                                        q.UpVotes,
                                        q.DownVotes,
                                        q.ContentHtml,
                                        q.Answers,
                                        q.PostedById,
                                        u.Username,
                                        q.ModelId,
                                        DeviceId = d.Id,
                                        ManufacturerId = mf.Id,
                                        q.IsResolved,
                                        ModelName = m.Name,
                                        DeviceName = d.Name,
                                        ManufacturerName = mf.Name
                                    };
                        if (modelId != 0)
                        {
                            query = query.Where(q => q.ModelId == modelId);
                        }
                        else
                        {
                            if (deviceId != 0)
                            {
                                query = query.Where(q => q.DeviceId == deviceId);
                            }
                            if (manufacturerId != 0)
                            {
                                query = query.Where(q => q.ManufacturerId == manufacturerId);
                            }
                        }
                        var total = query.Count();
                        query = query.Skip((pageInfo.PageNo - 1) * pageInfo.PageSize).Take(pageInfo.PageSize);
                        var questionList = new List<Question>();
                        foreach (var question in query)
                        {
                            questionList.Add(new Question
                            {
                                Id = question.Id,
                                Title = question.Title,
                                TitleUrl = question.TitleUrl,
                                PostedDate = question.PostedDate,
                                Views = question.Views,
                                Point = question.Point,
                                UpVotes = question.UpVotes,
                                DownVotes = question.DownVotes,
                                ContentHtml = question.ContentHtml,
                                Answers = question.Answers,
                                IsResolved = question.IsResolved,
                                PostedBy = new user { Id = question.PostedById, Username = question.Username },
                                //ModelWithAllInfo = new ModelWithAllInformation { Id = question.ModelId, ModelName = question.ModelName, DeviceName = question.DeviceName, ManufactureName = question.ManufacturerName }
                                Model = new Model { Id = question.ModelId, Name = question.ModelName, Manufacturer = new Manufacturer { Id = question.ManufacturerId, Name = question.ManufacturerName }, Device = new Device { Id = question.DeviceId, Name = question.DeviceName } },
                            });
                        }
                        return new QuestionListFeedback(true, null, questionList, total);
                    }

                   
                }

            }
            catch (Exception e)
            {

                return new QuestionListFeedback(false, "Cannot get list question");
            }
        }
        public QuestionListFeedback GetResolvedQuestion(PageInfo pageInfo, int deviceId, int manufacturerId, int modelId, string stringToSearch)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    if (stringToSearch != null && stringToSearch != "")
                    {
                        //var queryString = "SELECT * FROM Questions WHERE isResolved='true' and isDeleted='false' and dbo.Levenstein({0}, Title+convert(nvarchar,ContentHtml )) > 0 order by dbo.Levenstein({0}, Title+convert(nvarchar,ContentHtml )) desc";
                        var queryString =
                            "SELECT p.* FROM dbo.questions AS p INNER JOIN FREETEXTTABLE(questions, Title, {0}, LANGUAGE 'Vietnamese') AS ft ON ft.[KEY] = p.Id and isDeleted = 'false' and isResolved='true' ORDER BY ft.[Rank] DESC";
                       
                        var query = (ORMDbContext as System.Data.Entity.Infrastructure.IObjectContextAdapter)
                              .ObjectContext.ExecuteStoreQuery<Question>(queryString, stringToSearch).ToList();
                        var total = query.Count();
                        var result = query.Skip((pageInfo.PageNo - 1) * pageInfo.PageSize).Take(pageInfo.PageSize);
                        var questionList = new List<Question>();
                        foreach (var question in result)
                        {
                            question.Model = ORMDbContext.Models.SingleOrDefault(m => m.Id == question.ModelId);
                            var device = ORMDbContext.Devices.SingleOrDefault(m => m.Id == question.Model.DeviceId);
                            var manufacturer = ORMDbContext.Manufacturers.SingleOrDefault(m => m.Id == question.Model.ManufacturerId);
                            questionList.Add(new Question
                            {
                                Id = question.Id,
                                Title = question.Title,
                                TitleUrl = question.TitleUrl,
                                PostedDate = question.PostedDate,
                                Views = question.Views,
                                Point = question.Point,
                                UpVotes = question.UpVotes,
                                DownVotes = question.DownVotes,
                                ContentHtml = question.ContentHtml,
                                Answers = question.Answers,
                                IsResolved = question.IsResolved,
                                PostedBy = ORMDbContext.Users.SingleOrDefault(u => u.Id == question.PostedById),
                                //ModelWithAllInfo = new ModelWithAllInformation { Id = question.ModelId, ModelName = question.ModelName, DeviceName = question.DeviceName, ManufactureName = question.ManufacturerName }   
                                Model = new Model { Id = question.ModelId, Name = question.Model.Name, Manufacturer = new Manufacturer { Id = manufacturer.Id, Name = manufacturer.Name }, Device = new Device { Id = device.Id, Name = device.Name } },
                            });
                        }
                        return new QuestionListFeedback(true, null, questionList, total);
                    }
                    else
                    {
                        var query = from q in ORMDbContext.Questions
                                    join u in ORMDbContext.Users on q.PostedById equals u.Id
                                    join m in ORMDbContext.Models on q.ModelId equals m.Id
                                    join d in ORMDbContext.Devices on m.DeviceId equals d.Id
                                    join mf in ORMDbContext.Manufacturers on m.ManufacturerId equals mf.Id
                                    where !q.IsDeleted & q.IsResolved
                                    orderby q.PostedDate descending
                                    select new
                                    {
                                        q.Id,
                                        q.Title,
                                        q.TitleUrl,
                                        q.PostedDate,
                                        q.Views,
                                        q.Point,
                                        q.UpVotes,
                                        q.DownVotes,
                                        q.ContentHtml,
                                        q.Answers,
                                        q.PostedById,
                                        u.Username,
                                        q.ModelId,
                                        DeviceId = d.Id,
                                        ManufacturerId = mf.Id,
                                        q.IsResolved,
                                        ModelName = m.Name,
                                        DeviceName = d.Name,
                                        ManufacturerName = mf.Name
                                    };
                        if (modelId != 0)
                        {
                            query = query.Where(q => q.ModelId == modelId);
                        }
                        else
                        {
                            if (deviceId != 0)
                            {
                                query = query.Where(q => q.DeviceId == deviceId);
                            }
                            if (manufacturerId != 0)
                            {
                                query = query.Where(q => q.ManufacturerId == manufacturerId);
                            }
                        }
                        var total = query.Count();
                        query = query.Skip((pageInfo.PageNo - 1) * pageInfo.PageSize).Take(pageInfo.PageSize);
                        var questionList = new List<Question>();
                        foreach (var question in query)
                        {
                            questionList.Add(new Question
                            {
                                Id = question.Id,
                                Title = question.Title,
                                TitleUrl = question.TitleUrl,
                                PostedDate = question.PostedDate,
                                Views = question.Views,
                                Point = question.Point,
                                UpVotes = question.UpVotes,
                                DownVotes = question.DownVotes,
                                ContentHtml = question.ContentHtml,
                                Answers = question.Answers,
                                IsResolved = question.IsResolved,
                                PostedBy = new user { Id = question.PostedById, Username = question.Username },
                                //ModelWithAllInfo = new ModelWithAllInformation { Id = question.ModelId, ModelName = question.ModelName, DeviceName = question.DeviceName, ManufactureName = question.ManufacturerName }
                                Model = new Model { Id = question.ModelId, Name = question.ModelName, Manufacturer = new Manufacturer { Id = question.ManufacturerId, Name = question.ManufacturerName }, Device = new Device { Id = question.DeviceId, Name = question.DeviceName } },
                            });
                        }
                        return new QuestionListFeedback(true, null, questionList, total);
                    }
                   
                }

            }
            catch (Exception e)
            {

                return new QuestionListFeedback(false, "Cannot get list question");
            }
        }
        public QuestionListFeedback GetUnresolvedQuestion(PageInfo pageInfo, int deviceId, int manufacturerId, int modelId, string stringToSearch)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    if (stringToSearch != null && stringToSearch != "")
                    {
                        //var queryString = "SELECT * FROM Questions WHERE isResolved='false' and isDeleted='false' and dbo.Levenstein({0}, Title+convert(nvarchar,ContentHtml )) > 0 order by dbo.Levenstein({0}, Title+convert(nvarchar,ContentHtml )) desc";
                        var queryString =
                            "SELECT p.* FROM dbo.questions AS p INNER JOIN FREETEXTTABLE(questions, Title, {0}, LANGUAGE 'Vietnamese') AS ft ON ft.[KEY] = p.Id and isDeleted = 'false' and isResolved='false' ORDER BY ft.[Rank] DESC";
                       
                        var query = (ORMDbContext as System.Data.Entity.Infrastructure.IObjectContextAdapter)
                              .ObjectContext.ExecuteStoreQuery<Question>(queryString, stringToSearch).ToList();
                        var total = query.Count();
                        var result = query.Skip((pageInfo.PageNo - 1) * pageInfo.PageSize).Take(pageInfo.PageSize);
                        var questionList = new List<Question>();
                        foreach (var question in result)
                        {
                            question.Model = ORMDbContext.Models.SingleOrDefault(m => m.Id == question.ModelId);
                            var device = ORMDbContext.Devices.SingleOrDefault(m => m.Id == question.Model.DeviceId);
                            var manufacturer = ORMDbContext.Manufacturers.SingleOrDefault(m => m.Id == question.Model.ManufacturerId);
                            questionList.Add(new Question
                            {
                                Id = question.Id,
                                Title = question.Title,
                                TitleUrl = question.TitleUrl,
                                PostedDate = question.PostedDate,
                                Views = question.Views,
                                Point = question.Point,
                                UpVotes = question.UpVotes,
                                DownVotes = question.DownVotes,
                                ContentHtml = question.ContentHtml,
                                Answers = question.Answers,
                                IsResolved = question.IsResolved,
                                PostedBy = ORMDbContext.Users.SingleOrDefault(u => u.Id == question.PostedById),
                                //ModelWithAllInfo = new ModelWithAllInformation { Id = question.ModelId, ModelName = question.ModelName, DeviceName = question.DeviceName, ManufactureName = question.ManufacturerName }   
                                Model = new Model { Id = question.ModelId, Name = question.Model.Name, Manufacturer = new Manufacturer { Id = manufacturer.Id, Name = manufacturer.Name }, Device = new Device { Id = device.Id, Name = device.Name } },
                            });
                        }
                        return new QuestionListFeedback(true, null, questionList, total);
                    }
                    else
                    {
                        var query = from q in ORMDbContext.Questions
                                    join u in ORMDbContext.Users on q.PostedById equals u.Id
                                    join m in ORMDbContext.Models on q.ModelId equals m.Id
                                    join d in ORMDbContext.Devices on m.DeviceId equals d.Id
                                    join mf in ORMDbContext.Manufacturers on m.ManufacturerId equals mf.Id
                                    where !q.IsDeleted & !q.IsResolved
                                    orderby q.PostedDate descending
                                    select new
                                    {
                                        q.Id,
                                        q.Title,
                                        q.TitleUrl,
                                        q.PostedDate,
                                        q.Views,
                                        q.Point,
                                        q.UpVotes,
                                        q.DownVotes,
                                        q.ContentHtml,
                                        q.Answers,
                                        q.PostedById,
                                        u.Username,
                                        q.ModelId,
                                        DeviceId = d.Id,
                                        ManufacturerId = mf.Id,
                                        q.IsResolved,
                                        ModelName = m.Name,
                                        DeviceName = d.Name,
                                        ManufacturerName = mf.Name
                                    };
                        if (modelId != 0)
                        {
                            query = query.Where(q => q.ModelId == modelId);
                        }
                        else
                        {
                            if (deviceId != 0)
                            {
                                query = query.Where(q => q.DeviceId == deviceId);
                            }
                            if (manufacturerId != 0)
                            {
                                query = query.Where(q => q.ManufacturerId == manufacturerId);
                            }
                        }
                        var total = query.Count();
                        query = query.Skip((pageInfo.PageNo - 1) * pageInfo.PageSize).Take(pageInfo.PageSize);
                        var questionList = new List<Question>();
                        foreach (var question in query)
                        {
                            questionList.Add(new Question
                            {
                                Id = question.Id,
                                Title = question.Title,
                                TitleUrl = question.TitleUrl,
                                PostedDate = question.PostedDate,
                                Views = question.Views,
                                Point = question.Point,
                                UpVotes = question.UpVotes,
                                DownVotes = question.DownVotes,
                                ContentHtml = question.ContentHtml,
                                Answers = question.Answers,
                                IsResolved = question.IsResolved,
                                PostedBy = new user { Id = question.PostedById, Username = question.Username },
                                //ModelWithAllInfo = new ModelWithAllInformation { Id = question.ModelId, ModelName = question.ModelName, DeviceName = question.DeviceName, ManufactureName = question.ManufacturerName }
                                Model = new Model { Id = question.ModelId, Name = question.ModelName, Manufacturer = new Manufacturer { Id = question.ManufacturerId, Name = question.ManufacturerName }, Device = new Device { Id = question.DeviceId, Name = question.DeviceName } },
                            });
                        }
                        return new QuestionListFeedback(true, null, questionList, total);
                    }
                   
                }

            }
            catch (Exception e)
            {

                return new QuestionListFeedback(false, "Cannot get list question");
            }
        }
        public QuestionListFeedback GetUnansweredQuestion(PageInfo pageInfo, int deviceId, int manufacturerId, int modelId, string stringToSearch)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    if (stringToSearch != null && stringToSearch != "")
                    {
                        //var queryString = "SELECT * FROM Questions WHERE Answers=0 and isDeleted='false' and dbo.Levenstein({0}, Title+convert(nvarchar,ContentHtml )) > 0 order by dbo.Levenstein({0}, Title+convert(nvarchar,ContentHtml )) desc";
                        var queryString =
                            "SELECT p.* FROM dbo.questions AS p INNER JOIN FREETEXTTABLE(questions, Title, {0}, LANGUAGE 'Vietnamese') AS ft ON ft.[KEY] = p.Id and isDeleted = 'false' and Answers=0 ORDER BY ft.[Rank] DESC";
                       
                        var query = (ORMDbContext as System.Data.Entity.Infrastructure.IObjectContextAdapter)
                              .ObjectContext.ExecuteStoreQuery<Question>(queryString, stringToSearch).ToList();
                        var total = query.Count();
                        var result = query.Skip((pageInfo.PageNo - 1) * pageInfo.PageSize).Take(pageInfo.PageSize);
                        var questionList = new List<Question>();
                        foreach (var question in result)
                        {
                            question.Model = ORMDbContext.Models.SingleOrDefault(m => m.Id == question.ModelId);
                            var device = ORMDbContext.Devices.SingleOrDefault(m => m.Id == question.Model.DeviceId);
                            var manufacturer = ORMDbContext.Manufacturers.SingleOrDefault(m => m.Id == question.Model.ManufacturerId);
                            questionList.Add(new Question
                            {
                                Id = question.Id,
                                Title = question.Title,
                                TitleUrl = question.TitleUrl,
                                PostedDate = question.PostedDate,
                                Views = question.Views,
                                Point = question.Point,
                                UpVotes = question.UpVotes,
                                DownVotes = question.DownVotes,
                                ContentHtml = question.ContentHtml,
                                Answers = question.Answers,
                                IsResolved = question.IsResolved,
                                PostedBy = ORMDbContext.Users.SingleOrDefault(u => u.Id == question.PostedById),
                                //ModelWithAllInfo = new ModelWithAllInformation { Id = question.ModelId, ModelName = question.ModelName, DeviceName = question.DeviceName, ManufactureName = question.ManufacturerName }   
                                Model = new Model { Id = question.ModelId, Name = question.Model.Name, Manufacturer = new Manufacturer { Id = manufacturer.Id, Name = manufacturer.Name }, Device = new Device { Id = device.Id, Name = device.Name } },
                            });
                        }
                        return new QuestionListFeedback(true, null, questionList, total);
                    }
                    else
                    {
                        var query = from q in ORMDbContext.Questions
                                    join u in ORMDbContext.Users on q.PostedById equals u.Id
                                    join m in ORMDbContext.Models on q.ModelId equals m.Id
                                    join d in ORMDbContext.Devices on m.DeviceId equals d.Id
                                    join mf in ORMDbContext.Manufacturers on m.ManufacturerId equals mf.Id
                                    where !q.IsDeleted
                                    && q.Answers == 0
                                    orderby q.PostedDate descending
                                    select new
                                    {
                                        q.Id,
                                        q.Title,
                                        q.TitleUrl,
                                        q.PostedDate,
                                        q.Views,
                                        q.Point,
                                        q.UpVotes,
                                        q.DownVotes,
                                        q.ContentHtml,
                                        q.Answers,
                                        q.PostedById,
                                        u.Username,
                                        q.ModelId,
                                        DeviceId = d.Id,
                                        ManufacturerId = mf.Id,
                                        q.IsResolved,
                                        ModelName = m.Name,
                                        DeviceName = d.Name,
                                        ManufacturerName = mf.Name
                                    };
                        if (modelId != 0)
                        {
                            query = query.Where(q => q.ModelId == modelId);
                        }
                        else
                        {
                            if (deviceId != 0)
                            {
                                query = query.Where(q => q.DeviceId == deviceId);
                            }
                            if (manufacturerId != 0)
                            {
                                query = query.Where(q => q.ManufacturerId == manufacturerId);
                            }
                        }
                        var total = query.Count();
                        query = query.Skip((pageInfo.PageNo - 1) * pageInfo.PageSize).Take(pageInfo.PageSize);
                        var questionList = new List<Question>();
                        foreach (var question in query)
                        {
                            questionList.Add(new Question
                            {
                                Id = question.Id,
                                Title = question.Title,
                                TitleUrl = question.TitleUrl,
                                PostedDate = question.PostedDate,
                                Views = question.Views,
                                Point = question.Point,
                                UpVotes = question.UpVotes,
                                DownVotes = question.DownVotes,
                                ContentHtml = question.ContentHtml,
                                Answers = question.Answers,
                                IsResolved = question.IsResolved,
                                PostedBy = new user { Id = question.PostedById, Username = question.Username },
                                //ModelWithAllInfo = new ModelWithAllInformation { Id = question.ModelId, ModelName = question.ModelName, DeviceName = question.DeviceName, ManufactureName = question.ManufacturerName }
                                Model = new Model { Id = question.ModelId, Name = question.ModelName, Manufacturer = new Manufacturer { Id = question.ManufacturerId, Name = question.ManufacturerName }, Device = new Device { Id = question.DeviceId, Name = question.DeviceName } },
                            });
                        }
                        return new QuestionListFeedback(true, null, questionList, total);
                    }
                    
                }

            }
            catch (Exception e)
            {

                return new QuestionListFeedback(false, "Cannot get list question");
            }
        }

        public Feedback<Question> InsertQuestion(Question question)
        {
            var qnew = new Question
            {
                Title = question.Title,
                TitleUrl = question.TitleUrl,
                ModelId = question.ModelId,
                ContentHtml = question.ContentHtml,
                Views = 0,
                Answers = 0,
                Reports = 0,
                PostedById = question.PostedById,
                PostedDate = DateTime.Now,
                IsDeleted = false,
                IsResolved = false,
                UpdatedBy = null,
                LastUpdate = null,
                Point = 0,
                UpVotes = 0,
                DownVotes = 0,
            };

            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    // Add to DB
                    ORMDbContext.Questions.Add(qnew);
                    ORMDbContext.SaveChanges();
                    return new Feedback<Question>(true, null, qnew);
                }

            }
            catch (Exception e)
            {
                Logger.Error(e);
                return new Feedback<Question>(false, Messages.GeneralError);
            }
        }
        public Feedback<Question> UpdateQuestion(Question question)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    var Question = ORMDbContext.Questions.SingleOrDefault(i => i.Id == question.Id && !question.IsDeleted);

                    if (Question == null)
                        return new Feedback<Question>(false, "question khong ton tai");

                    Question.Title = question.Title;
                    Question.TitleUrl = question.TitleUrl;
                    Question.ModelId = question.ModelId;
                    Question.ContentHtml = question.ContentHtml;

                    Question.UpdatedById = question.UpdatedById;
                    Question.LastUpdate = DateTime.Now;

                    ORMDbContext.SaveChanges();

                    return new Feedback<Question>(true, null, Question);
                }
            }
            catch (Exception e)
            {
                Logger.Error(e);
                return new Feedback<Question>(false, Messages.GeneralError);
            }
        }

        public Feedback DeleteQuestion(int id)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    Question q = ORMDbContext.Questions.Single(i => i.Id == id);
                    q.IsDeleted = true;
                    ORMDbContext.SaveChanges();
                    return new Feedback(true);
                }

            }
            catch (Exception e)
            {
                Logger.Error(e);
                return new Feedback(false, Messages.GeneralError);
            }
        }

        public Feedback<Question> VoteQuestion(int questionId, int userId)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    var question = ORMDbContext.Questions.Single(i => i.Id == questionId);
                    var questionVote = ORMDbContext.VotedQuestions.SingleOrDefault(t => t.QuestionId == questionId && t.VotedById == userId);

                    //Do not allow seft voting
                    if (question.PostedById == userId)
                    {
                        return new Feedback<Question>(false, Messages.VoteQuestion_SeftVoting, null);
                    }

                    //Already voted up --> Remove this vote
                    if (questionVote != null)
                    {
                        ORMDbContext.VotedQuestions.Remove(questionVote);
                        question.UpVotes--;
                    }
                    //Not vote yet (Insert)
                    else
                    {
                        questionVote = new VotedQuestion()
                        {
                            IsUpVote = true,
                            QuestionId = questionId,
                            VotedById = userId,
                            VotedDate = DateTime.Now,
                        };
                        ORMDbContext.VotedQuestions.Add(questionVote);
                        question.UpVotes++;
                    }

                    ORMDbContext.SaveChanges();

                    return new Feedback<Question>(true, null, question);
                }

            }
            catch (Exception e)
            {
                Logger.Error(e);
                return new Feedback<Question>(false, Messages.GeneralError);
            }
        }

        public Feedback<Question> ReportQuestion(int questionId, string reason, int userId)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    var question = ORMDbContext.Questions.Single(i => i.Id == questionId);
                    var questionReport = ORMDbContext.ReportedQuestions.SingleOrDefault(t => t.QuestionId == questionId && t.ReportedById == userId);

                    //Already reported --> Remove this report
                    if (questionReport != null)
                    {
                        ORMDbContext.ReportedQuestions.Remove(questionReport);
                        question.Reports--;
                    }
                    //Not reported yet (Insert)
                    else
                    {
                        questionReport = new ReportedQuestion()
                        {
                            QuestionId = questionId,
                            Reason = reason,
                            ReportedById = userId,
                            ReportedDate = DateTime.Now,
                            IsIgnored = false,
                        };
                        ORMDbContext.ReportedQuestions.Add(questionReport);
                        question.Reports++;
                    }

                    ORMDbContext.SaveChanges();

                    return new Feedback<Question>(true, null, question);
                }

            }
            catch (Exception e)
            {
                Logger.Error(e);
                return new Feedback<Question>(false, Messages.GeneralError);
            }
        }

        public QuestionListFeedback GetSimilarQuestions(int modelId, string stringToSearch)
        {

            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    //var queryString = "SELECT TOP 5 * FROM Questions WHERE isDeleted='false' and modelId={0} and dbo.Levenstein({1}, Title) > 0 order by dbo.Levenstein({1}, Title) desc";
                    //var queryString =
                    //        "SELECT TOP 5 p.* FROM dbo.questions AS p INNER JOIN FREETEXTTABLE(questions, Title, {0}, LANGUAGE 'Vietnamese') AS ft ON ft.[KEY] = p.Id and isDeleted = 'false' and modelId={1} ORDER BY ft.[Rank] DESC";
                       
                    //var query = (ORMDbContext as System.Data.Entity.Infrastructure.IObjectContextAdapter)
                    //      .ObjectContext.ExecuteStoreQuery<Question>(queryString, stringToSearch, modelId).ToList();
                    var queryString =
                            "SELECT TOP 5 p.* FROM dbo.questions AS p INNER JOIN FREETEXTTABLE(questions, Title, {0}, LANGUAGE 'Vietnamese') AS ft ON ft.[KEY] = p.Id and isDeleted = 'false' ORDER BY ft.[Rank] DESC";

                    var query = (ORMDbContext as System.Data.Entity.Infrastructure.IObjectContextAdapter)
                          .ObjectContext.ExecuteStoreQuery<Question>(queryString, stringToSearch).ToList();
                    var total = query.Count();
                    var questionList = new List<Question>();
                    foreach (var question in query)
                    {
                        questionList.Add(new Question
                        {
                            Id = question.Id,
                            Title = question.Title,
                        });
                    }
                    return new QuestionListFeedback(true, null, questionList, total);

                }
            }
            catch (Exception e)
            {

                return new QuestionListFeedback(false, "Cannot get list question");
            }
        }

        public QuestionListFeedback GetQuestionBookmarks(int userId, PageInfo pageInfo)
        {

            try
            {
                using (var ORMDbContext = new OrmEntities())
                {                    
                        var query = from q in ORMDbContext.Questions
                                    join qb in ORMDbContext.QuestionBookmarks on q.Id equals qb.QuestionId
                                    where !q.IsDeleted && qb.BookmarkById == userId
                                    orderby qb.BookmarkDate descending
                                    select new
                                    {
                                        q.Id,
                                        q.Title,
                                    };                        
                        var total = query.Count();
                        query = query.Skip((pageInfo.PageNo - 1) * pageInfo.PageSize).Take(pageInfo.PageSize);
                        var questionList = new List<Question>();
                        foreach (var question in query)
                        {
                            questionList.Add(new Question
                            {
                                Id = question.Id,
                                Title = question.Title,
                          });
                        }
                        return new QuestionListFeedback(true, null, questionList, total);

                    }
            }
            catch (Exception e)
            {

                return new QuestionListFeedback(false, "Cannot get list question");
            }
        }

        public Feedback<IList<Question>> GetQuestionBookmarks(int userId)
        {

            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    var query = from q in ORMDbContext.Questions
                                join qb in ORMDbContext.QuestionBookmarks on q.Id equals qb.QuestionId
                                join u in ORMDbContext.Users on q.PostedById equals u.Id
                                where !q.IsDeleted && qb.BookmarkById == userId
                                orderby qb.BookmarkDate descending
                                select new
                                {
                                    q.Id,
                                    q.Title,
                                    q.PostedById,
                                    u.Username,
                                    q.PostedDate,
                                    q.Views,
                                    q.UpVotes,
                                    q.Answers,
                                    q.IsResolved,
                                };
                    var questionList = new List<Question>();
                    foreach (var question in query)
                    {
                        questionList.Add(new Question
                        {
                            Id = question.Id,
                            Title = question.Title,
                            PostedBy = new user { Id = question.PostedById, Username = question.Username },
                            PostedDate = question.PostedDate,
                            Views = question.Views,
                            UpVotes = question.UpVotes,
                            Answers = question.Answers,
                            IsResolved = question.IsResolved,
                        });
                    }
                    return new Feedback<IList<Question>>(true, null, questionList);

                }
            }
            catch (Exception e)
            {

                Logger.Error(e);
                return new Feedback<IList<Question>>(false, Messages.GeneralError);
            }
        }


        public Feedback<Question> AddBookmarkAnQuestion(int questionId, int userId)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    var question = ORMDbContext.Questions.Single(i => i.Id == questionId);
                    var questionBookmark = ORMDbContext.QuestionBookmarks.SingleOrDefault(t => t.QuestionId == questionId && t.BookmarkById == userId);

                    //Already add bookmark --> Remove it
                    if (questionBookmark != null)
                    {
                        ORMDbContext.QuestionBookmarks.Remove(questionBookmark);
                        //question.Reports--;
                    }
                    //Not reported yet (Insert)
                    else
                    {
                        questionBookmark = new QuestionBookmark()
                        {
                            QuestionId = questionId,
                            BookmarkById = userId,
                            BookmarkDate = DateTime.Now,
                        };
                        ORMDbContext.QuestionBookmarks.Add(questionBookmark);
                        //question.Reports++;
                    }

                    ORMDbContext.SaveChanges();

                    return new Feedback<Question>(true, null, question);
                }

            }
            catch (Exception e)
            {
                Logger.Error(e);
                return new Feedback<Question>(false, Messages.GeneralError);
            }
        }
        public Feedback<Question> MarkQuestionAsResolved(int questionId)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    var question = ORMDbContext.Questions.Single(i => i.Id == questionId);
                    question.IsResolved = true;
                    ORMDbContext.SaveChanges();
                    return new Feedback<Question>(true, null, question);
                }

            }
            catch (Exception e)
            {
                Logger.Error(e);
                return new Feedback<Question>(false, Messages.GeneralError);
            }
        }
        public Feedback<Question> DeleteAnAnswer(int questionId)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    var question = ORMDbContext.Questions.Single(i => i.Id == questionId);
                    question.Answers--;
                    ORMDbContext.SaveChanges();
                    return new Feedback<Question>(true, null, question);
                }

            }
            catch (Exception e)
            {
                Logger.Error(e);
                return new Feedback<Question>(false, Messages.GeneralError);
            }
        }
        public bool CheckIfAnUserCanAcceptAnswerForAQuestion(int questionId, int userId)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    var question = ORMDbContext.Questions.Single(i => i.Id == questionId);
                    if (!question.IsResolved && question.PostedById == userId)
                    {
                        return true;
                    }
                    return false;
                }

            }
            catch (Exception e)
            {
                Logger.Error(e);
                return false;
            }
        }
        #endregion

        #region Reported Question

        public Feedback<IList<Question>> GetAllReportedQuestion(int minReportNumber)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    var query = from q in ORMDbContext.Questions
                                    from u in ORMDbContext.Users
                                    where !q.IsDeleted && q.Reports >= minReportNumber && q.PostedById == u.Id
                                    orderby q.Reports descending
                                    select new
                                    {
                                        q.Id,
                                        q.Title,
                                        q.TitleUrl,
                                        q.Reports,
                                        q.PostedDate,
                                        q.PostedById,
                                        u.Username
                                    };
                    List<Question> rq = new List<Question>();
                    foreach (var q in query)
                    {
                        rq.Add(new Question
                        {
                            Id = q.Id,
                            Title = q.Title,
                            TitleUrl = q.TitleUrl,
                            PostedDate = q.PostedDate,
                            Reports = q.Reports,
                            PostedBy = new user
                            {
                                Id = q.PostedById,
                                Username = q.Username
                            },
                        });
                    }

                    return new Feedback<IList<Question>>(true, null, rq);
                }

            }
            catch (Exception e)
            {
                Logger.Error(e);
                return new Feedback<IList<Question>>(false, Messages.GeneralError);
            }
        }

        public Feedback<IList<ReportedQuestion>> GetReportedQuestionDetails(int id)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    var query = ORMDbContext.ReportedQuestions.Where(i => i.QuestionId == id)
                        .Select(i => new
                        {
                            i.Id,
                            i.ReportedDate,
                            i.Reason,
                            i.ReportedBy.Username
                        });

                    List<ReportedQuestion> rq = new List<ReportedQuestion>();
                    foreach (var q in query)
                    {
                        rq.Add(new ReportedQuestion
                        {
                            Id = q.Id,
                            ReportedDate = q.ReportedDate,
                            Reason = q.Reason,
                            ReportedBy = new user
                            {
                                Username = q.Username
                            }
                        });
                    }
                    return new Feedback<IList<ReportedQuestion>>(true, null, rq);

                }

            }
            catch (Exception e)
            {
                Logger.Error(e);
                return new Feedback<IList<ReportedQuestion>>(false, Messages.GeneralError);
            }
        }

        public Feedback DeleteReportedQuestion(int id)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    Question q = ORMDbContext.Questions.Single(i => i.Id == id);
                    q.IsDeleted = true;
                    user User = ORMDbContext.Users.Find(q.PostedById);
                    User.Ponit += KeyObject.PointConfig.Spam;
                    ORMDbContext.SaveChanges();

                    return new Feedback(true);
                }

            }
            catch (Exception e)
            {
                Logger.Error(e);
                return new Feedback(false, Messages.GeneralError);
            }
        }

        public Feedback DeleteFalseReports(int id)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    // Delete attached reports of the quesion
                    List<ReportedQuestion> rq = ORMDbContext.ReportedQuestions.Where(i => i.QuestionId == id).ToList();
                    foreach (ReportedQuestion i in rq)
                    {
                        i.IsIgnored = true;
                    }
                    //set report number to zero
                    Question q = ORMDbContext.Questions.Single(i => i.Id == id);
                    q.Reports = 0;
                    ORMDbContext.SaveChanges();

                    return new Feedback(true);
                }

            }
            catch (Exception e)
            {
                Logger.Error(e);
                return new Feedback(false, Messages.GeneralError);
            }
        }

        #endregion
        
    }
}